/***********************************************************************************************************************************
 *    Name:	Job_PurgeHistory.sql
 *  Author: Frank Figearo — http://sqlnerd.me/ — frank@sqlnerd.me
 * Summary:	
 */
USE msdb;
GO

BEGIN TRY; EXECUTE msdb.dbo.sp_delete_job @job_name= N'syspolicy_purge_history', @delete_unused_schedule= 1;
END TRY BEGIN CATCH END CATCH;
GO

-- [Purge History] =================================================================================================================
DECLARE
	@JobName		NVARCHAR(128),
	@OutputFileName	NVARCHAR(255),
	@jobID			BINARY(16),
	@command		NVARCHAR(MAX);

SELECT
	@JobName		= N'• Purge History',
	@OutputFileName	= master.DBAdmin.GetInstancePath(N'WorkingDirectory') + N'PurgeHistory.txt';

SELECT @jobID= job_id FROM dbo.sysjobs WHERE name = @JobName;
IF @jobID IS Null BEGIN
	EXECUTE dbo.sp_add_job
		@job_name			= @JobName,
		@category_name		= N'DBAdmin',
		@owner_login_name	= N'sa',
		@job_id				= @jobID OUTPUT;
	EXECUTE dbo.sp_add_jobserver @job_id= @jobID, @server_name= N'(LOCAL)';
END ELSE EXECUTE dbo.sp_delete_jobstep @job_id= @jobID, @step_id= 0;

EXECUTE dbo.sp_update_job
	@job_id						= @jobID,
	@enabled					= 1,
	@notify_level_page			= 0,	-- Never
	@notify_level_netsend		= 0,	-- Never
	@notify_level_eventlog		= 0,	-- Never
	@notify_level_email			= 2,	-- On Failure
	@notify_email_operator_name	= N'(DB)A-Team';

IF 9 < master.DBAdmin.ProductVersion() BEGIN
	SET @command= N'(Get-Item SQLServer:\SQLPolicy\' + CAST(SERVERPROPERTY('MachineName') AS NVARCHAR(128)) + N'\' + ISNULL(CAST(SERVERPROPERTY('InstanceName') AS NVARCHAR(128)), N'Default') + N').EraseSystemHealthPhantomRecords();';
	EXECUTE dbo.sp_add_jobstep
		@job_id				= @jobID,
		@step_name			= N'Erase Phantom System Health Records.',
		@database_name		= N'master',
		@on_success_action	= 3,	-- Go to Next Step
		@subsystem			= N'PowerShell',
		@command			= @command;
END;

EXECUTE dbo.sp_add_jobstep
	@job_id				= @jobID,
	@step_name			= N'Purge History',
	@database_name		= N'master',
	@command			= N'EXECUTE DBAdmin.PurgeHistory;',
	@output_file_name	= @OutputFileName;

BEGIN TRY;
  EXECUTE msdb.dbo.sp_delete_schedule @schedule_name= @JobName, @force_delete= 1;
END TRY BEGIN CATCH END CATCH;
EXECUTE msdb.dbo.sp_add_schedule
	@schedule_name			= @JobName,
	@enabled				= 1,
	@freq_type				= 8,
	@freq_interval			= 1,
	@freq_subday_type		= 1,
	@freq_subday_interval	= 0,
	@freq_relative_interval	= 0,
	@freq_recurrence_factor	= 1,
	@active_start_date		= 20120920,
	@active_end_date		= 99991231,
	@active_start_time		= 231213,
	@active_end_time		= 235959;
BEGIN TRY;
  EXECUTE msdb.dbo.sp_attach_schedule @job_name= @JobName, @schedule_name= @JobName;
END TRY BEGIN CATCH END CATCH;
GO